<?php

class  Users_model extends CI_Model{
    public function __construct(){
        $this->load->database();
        $this->load->helper("url");
        $this->load->model('users_model');
        $this->load->model('manager_model',"manager");
    }

    // 员工管理 列表 cj
    public function get_user_orders($requst){

        $manager_id = $requst['manager_id'];

        if($requst['customer_tel'])
        {
            $desc['customer_tel'] = $requst['customer_tel'];
        }
        if($requst['customer_name'])
        {
            $desc['customer_name'] = $requst['customer_name'];
        }
        if($requst['company'])
        {
            $desc['company'] = $requst['company'];
        }
        if($requst['order_status'])
        {
            $desc['order_status'] = $requst['order_status'];
            if($desc['order_status'] == 2)
            {
                // 默认三个月
                $desc['order_month'] = $requst['order_month']&&is_numeric($requst['order_month'])?$requst['order_month']:3;
            }
        }else{
            // 默认全部
            $desc['order_status'] = 1;
        }

        if($requst['order_sort'])
        {
            $desc['order_sort'] = $requst['order_sort'];
        }else{
            $desc['order_sort'] = 1;
        }
        if($requst['asc_type'])
        {
            $desc['asc_type'] = $requst['asc_type']==1 ? "ASC" : "DESC";
        }else{
            $desc['asc_type'] = "DESC";
        }

        $desc['page'] = $requst['page']?(int)$requst['page']:1;
        $desc['psize'] = $requst['psize']?(int)$requst['psize']:10;

        $ext_sql = '';
        $ext_sql_where = '';
        // 全部单
        if($desc['order_status'] == 1)
        {

        }
        // 未下单
        if($desc['order_status'] == 2)
        {
            $ext_sql_where .= "       
             AND c.id NOT IN (
                SELECT customer_id
            FROM
                customer_order
            WHERE
                customer_order.order_date > DATE_SUB(
                    CURDATE(),
                    INTERVAL ".$desc['order_month']." MONTH)
            GROUP BY customer_id
            
            )";
        }

        // 已下单
        if($desc['order_status'] == 3)
        {
            $ext_sql .= "       
             AND order_status <> '等待买家付款' ";
        }

        // 对会员表的筛选
        if($desc['customer_tel'])
        {
            $ext_sql_where .= "       
             AND c.customer_tel LIKE '%".$desc['customer_tel']."%' ";
        }
        if($desc['customer_name'])
        {
            $ext_sql_where .= "       
             AND c.customer_name  LIKE '%".$desc['customer_name']."%' ";
        }
        if($desc['company'])
        {
            $ext_sql_where .= "       
             OR c.company  LIKE '%".$desc['company']."%' ";
        }

        $sql = "
            SELECT
                c.id as customer_id,
                c.manager_id,
                c.customer_name,
                c.customer_tel,
                c.customer_email,
                c.company,
                c.position,
                c.discount,
                c.adjustments,
                c.created_at,
                c.last_login,
                c.pic_path,
	            IFNULL(co.ordercount,0) as ordercount,
	            IFNULL(cr.recordcount,0) as recordcount
            FROM
                customer c
                LEFT JOIN (
	SELECT
		manager_id,
		customer_id,
		COUNT(customer_id) AS ordercount
	FROM
		customer_order
	WHERE
		1 = 1
	".$ext_sql."
	GROUP BY
            customer_id
    ) co ON c.id = co.customer_id
    AND c.manager_id = co.manager_id
    LEFT JOIN (
        SELECT
            manager_id,customer_id, COUNT(customer_id) as recordcount
        FROM
            customer_record
        GROUP BY
            customer_id
    ) cr ON cr.customer_id = c.id
    AND cr.manager_id = c.manager_id
            WHERE
                c.manager_id = " .$manager_id ." 
                ".$ext_sql_where."
            GROUP BY
                c.id";

        switch ($desc['order_sort'])
        {
            case 1:
                $sql.=" ORDER BY c.created_at ".$desc['asc_type'].", ordercount ";
                break;
            case 2:
                $sql.=" ORDER BY c.created_at ";
                break;
            case 3:
                $sql.=" ORDER BY c.last_login ";
                break;
            case 4:
                $sql.=" ORDER BY ordercount ";
                break;
            default:
        }

        // 排序
        $sql.=$desc['asc_type'];
        //
        $limit_from = ($desc['page']-1)*$desc['psize'];
        $sql.=" limit ".$limit_from.",".$desc['psize'];


        $query = $this->db->query($sql);

        $info =  $query->result_array();

        if($info && count($info)>0){
            return $info;
        }else{
            return false;
        }


    }

    public function  get_info($id =0){
        if ($id){
            $info = $this->get_one($id);
            if($info){
                $info = changecontent($info);
                $info['pic_path']= $info['pic_path']?$this->config->item("upload_url").$info['pic_path']:"";
                //如果
                if ($info['is_bind']=="已绑定"){
                    $sql = "select mg2.name parent_name,mg2.identity parent_identity,mg2.id parent_id from manager mg2 inner join manager_bind bind ON bind.parent_id = mg2.id   WHERE  bind.manager_id=".$id;

                    $query = $this->db->query($sql);
                    $info1 =  $query->result_array();
                    if ($info1){
                        $info['parent_name'] = $info1[0]['parent_name'];
                        $info['parent_identity'] = $info1[0]['parent_identity'];
                        $info['parent_id'] = $info1[0]['parent_id'];
                    }
                    // 新增，如果绑定对象是销售经理，查询是否有上级
                    // 查询上级
                    if(preg_match('/销售经理/',$info['identity']))
                    {
                        $qr = $this->get_one_father_id($info['id'],'大客户经理');
                        if($qr)
                        {
                            $parent_info = $this->db->where(array('id'=>$qr))->get("manager")->row_array();
                            $info['parent_name'] = $parent_info['name'];
                            $info['parent_id'] = $parent_info['id'];
                            $info['parent_identity'] = $parent_info['identity'];
                        }
                    }
                }

                // 大区经理
                if($info['identity'] === '大区经理')
                {
                    $binfo = $this->get_boss_info();
                    $info['parent_id'] = '';
                    $info['parent_identity'] = $binfo['cso_name'];
                    $info['parent_name'] = $binfo['cso_name'];
                }
                return $info;
            }else{
                return false;
            }

            }else{
                return false;
            }


    }

    // 获取总监信息 cj
    function get_boss_info(){
        $bos_info = $this->db->get("key_value")->result_array();
        if($bos_info)
        {
            foreach ($bos_info as $binfo)
            {
                $res[$binfo['key']] = $binfo['value'];
            }
            return $res;
        }

        return false;
    }


    function get_one($id=0){
        if ($id){
            $sql = "select mg.* from manager mg WHERE mg.is_deleted=0 and  mg.id=".$id;

            $query = $this->db->query($sql);

            $info =  $query->result_array();

            if($info && count($info)>0){
                return $info[0];
            }else{
                return false;
            }

        }else{
            return false;
        }
    }
    //根据手机号查询经理
    function get_one_by_tel($tel=""){
        if ($tel){
            $sql = "select mg.* from manager mg WHERE mg.is_deleted=0 and  mg.tel='".$tel."'";

            $query = $this->db->query($sql);

            $info =  $query->result_array();

            if($info && count($info)>0){
                return $info[0];
            }else{
                return false;
            }

        }else{
            return false;
        }
    }
    //根据姓名查询除自己以外的其他
    function get_other($name="",$usr_id=0){
        if ($name){
            $sql = "select mg.* from manager mg WHERE mg.is_deleted=0 and mg.name='".$name."'";
            if($usr_id){
                $sql.=" and mg.id <> $usr_id";
            }
            $query = $this->db->query($sql);

            $info =  $query->result_array();

            if($info && count($info)>0){
                return $info[0];
            }else{
                return false;
            }
        }else{
            return false;
        }
    }

    //销售经理详情
    function manager_detail($id = 0,$pid=0,$page=1,$limit=3){
        if ($id && $pid){
            $info = $this->users_model->get_manager_base($id,$pid);
            if ($info){
                $money = $this->users_model->get_manager_money($id,$pid);
                $info['order_count'] = $money['order_count'];
                $info['money_sum'] = $money['money_sum'];
                $info['ct_count'] = $money['ct_count'];
                $month = date("Y-m");
                $info['month_money'] = $this->users_model->get_manager_money($id,$pid,$month);
                $info['month_money']["month"]=$month;
                //维护记录
                $record = $this->users_model->get_manager_record($id,$pid,$page,$limit);
                $info['record'] = $record;
                $data['status'] = "success";
                $data['msg'] = "获取成功";
                $data['data'] = $info;
            }else{
                $data['msg'] = "无此经理";
                $data['status'] = "fail";
                $data['data'] = "";
            }
        }else{
            $data['msg'] = "参数错误";
            $data['status'] = "fail";
            $data['data'] = "";
        }
        return $data;
    }

    /**
     * @param int $manager_id  销售经理id
     * @param int $p_id   大客户经理id
     * @param string $status 绑定状态
     * @param string $reason 绑定理由
     * @return mixed
     */
    function bind($manager_id= 0,$p_id= 0,$status="",$reason="",$check_status=false){
        // 绑定对象不能级别高于被绑对像
        $manager_info = $this->db->where(array("id"=>$manager_id))->get("manager")->row_array();
        $m_lv = identify_order($manager_info['identity']);
        $p_info = $this->db->where(array("id"=>$p_id))->get("manager")->row_array();
        $p_lv = identify_order($p_info['identity']);
        if($p_lv < $m_lv)
        {
            $bs = 'fail';
            $msg = '绑定失败，不可和上级绑定';
            return array('status'=>$bs,'msg'=>$msg);
        }
        //是否已经存在
        $info = $this->db->where(array("manager_id"=>$manager_id))->get("manager_bind")->result_array();
        $bs = 'success';
        $msg = '成功';
        //开启事务
        $this->db->trans_begin();
        if ($info){
            $map['manager_id'] = $manager_id;
//            $data1['bind_status'] = $status;
            $data1['parent_id'] = $p_id;
            $data1['bind_time'] = date("Y-m-d H:i:s");
//            if ($reason){
//                $data1['reason'] = $reason;
//            }
            $this->db->where($map)->update("manager_bind",$data1);
            $bool =  $this->db->affected_rows();
        }else{
            if($check_status)
            {
                // 更新status审核表 cj
                $dat['status'] = $status;
                $dat['manager_id'] = $manager_id;
                $dat['parent_id'] = $p_id;
                $dat['reason'] = $reason;
                $res = $this->check_bind_status($dat);
                if($res['status'] != 'success'){
                    $bs = 'fail';
                    $msg = $res['msg'];
                    return array('status'=>$bs,'msg'=>$msg);
                }
            }
            $data1['parent_id'] = $p_id;
            $data1['manager_id'] = $manager_id;
//            $data1['bind_status'] = $status;
            $data1['bind_time'] = date("Y-m-d H:i:s");
//            if ($reason){
//                $data1['reason'] = $reason;
//            }
            // 往上绑定时候判断是否是其子类 cj
            $this->find_all_father_id($p_id,'manager_id','parent_id','manager_bind',$f_re);
            if(in_array($manager_id,$f_re))
            {
                $bs = 'fail';
                $msg = '绑定失败，不可和上级绑定';
                $this->db->trans_rollback();
                return array('status'=>$bs,'msg'=>$msg);
            }
            unset($f_re);
            if(!$check_status)
            {
                $this->db->insert("manager_bind",$data1);
                $bool =  $this->db->affected_rows();
            }else{
                // 如果要审核不需要存manager_bind
                $bool = 1;
            }

        }
        if($bool){
            if($status!="申请绑定中"){
                // 试合作销售经理 加分
                if($manager_info['identity']=='试合作销售经理')
                {
                    // 只有第一次绑定时候才会加分
                    $binfo = $this->db->where(array("manager_id"=>$manager_id))->get("manager_bind_history")->row_array();
                    if(!$binfo)
                    {
                        find_all_father_id($this->db,$p_id,$rr);
                        $rr[] = $p_id;
                        foreach ($rr as $r)
                        {
                            $pinfo = $this->db->where(array("id"=>$r))->get("manager")->row_array();
                            if(!$pinfo['job_time'] && $pinfo['identity'] != '合作销售经理' && $pinfo['identity'] != '特聘大客户经理')
                            {
                                // 上级还在试用期的加分
                                $this->db->insert("score",array('manager_id'=>$pinfo['id'],'score'=>1,'reason'=>'每增加一名销售经理加1分','created_at'=>date('Y-m-d H:i:s',time()),'new_manager_id'=>$manager_id));
                            }
                        }
                    }
                }

                $his['parent_id'] = $p_id;
                $his['manager_id'] = $manager_id;
                $his['start_time'] = date("Y-m-d H:i:s");
                $his['end_time'] = "2099-12-31 23:59:59";
                $this->db->insert("manager_bind_history",$his);
                unset($map);
                $map['id'] = $manager_id;
                $up_mg['is_bind'] = "已绑定";
                $this->db->where($map)->update("manager",$up_mg);

                /**
                 * 高级大区经理|大区经理|资深大客户经理|特聘大客户经理|大客户经理|合作销售经理|试合作销售经理
                 * 只有 $manager_info 中 试合作销售经理 特聘大客户经理 不算招募
                 */
                $date = date("Y-m");
                if($manager_info['identity']!='试合作销售经理' && $manager_info['identity']!='特聘大客户经理')
                {
                    find_all_father_id($this->db,$p_id,$rr);
                    $rr[] = $p_id;
                    foreach ($rr as $v)
                    {
                        //给所有上级的norm完成招募指标+1
                        $this->users_model->add_finish_norm($v,$date,0,1);
                    }
                    //如果存在父级，给父级的直招under_recruit +1
                    $sql = "select * from manager_bind where manager_id = " .$p_id ;
                    $row = $this->db->query($sql)->row_array();
                    if ($row){
                        $this->users_model->add_finish_norm($row['parent_id'],$date,0,0,0,1);
                    }
                    // 给自己招募my_recruit +1
                    $this->users_model->add_finish_norm($p_id,$date,0,0,1);
                }
            }
            $this->db->trans_commit();
        }else{
            $bs = 'fail';
            $msg = '存储失败';
            $this->db->trans_rollback();
        }
        return array('status'=>$bs,'msg'=>$msg);

    }


    // 寻找所有上级
    function find_all_father_id($id,$id_name,$parent_name,$table, &$res = ''){
        $sql = "SELECT
                    $id_name,
                    $parent_name
                FROM
                    $table
                WHERE
                    1 = 1
                AND $id_name = ".$id .
            " limit 1";
        $rs = $this->db->query($sql)->row_array();
        if($rs){
            $res[] = $rs[$parent_name];
            $this->find_all_father_id($rs[$parent_name],$id_name,$parent_name,$table,$res);
        }else{
            return $res;
        }

    }


    // 寻找上级 到某个角色为止
    function get_one_father_id($id,$identity_name)
    {
        $sql =  "SELECT
                        mb.manager_id,
                        mb.parent_id,
                        m.identity
                    FROM
                        manager_bind AS mb RIGHT JOIN
                        manager AS m
                    ON
                        manager_id = id
                    where m.id = $id";
        $rs = $this->db->query($sql)->row_array();
        if(!preg_match("/$identity_name/",$rs['identity']) && $rs['parent_id']){
            return $this->get_one_father_id($rs['parent_id'],$identity_name);
        }else{
            if(preg_match("/$identity_name/",$rs['identity']))
            {
                return $id;
            }else{
                return '';
            }
        }
    }

    // 可绑定性校验 更新审核表
    function check_bind_status($data){
        $status_arr = array('申请绑定中','绑定成功','绑定失败','申请解绑中','解绑成功','解绑失败');
        $msg = '';
        $st = 'success';
        $sql = "SELECT
                    manager_id,
                    parent_id,
                    `status`
                FROM
                    manager_bind_status
                WHERE
                    1 = 1
                AND manager_id = ".$data['manager_id'] .
                " ORDER BY created_at DESC limit 1";


        $rs = $this->db->query($sql)->result_array();
        $st = 'success';
        $msg = '可以申请';
        if($rs){
            switch ($rs[0]['status'])
            {
                case $status_arr[0]:
                    $msg = '该经理正在申请绑定';
                    $st = 'fail';
                    break;
                case $status_arr[1]:
                    $msg = '该经理已被绑定';
                    $st = 'fail';
                    break;
                case $status_arr[2]:
                    break;
                case $status_arr[3]:
                    $msg = '该经理已被绑定，尚未解绑';
                    $st = 'fail';
                    break;
                case $status_arr[4]:
                    break;
                case $status_arr[5]:
                    $msg = '该经理已被绑定，尚未解绑';
                    $st = 'fail';
                    break;
            }
        }
        if($st == 'success')
        {
            $insertdata['manager_id'] = $data['manager_id'];
            $insertdata['parent_id'] = $data['parent_id'];
            $insertdata['status'] = $data['status']?$data['status']:'申请绑定中';
            $insertdata['reason'] = $data['reason']?$data['reason']:'';
            $insertdata['created_at'] = date("Y-m-d H:i:s");
            $this->db->insert("manager_bind_status",$insertdata);
            $bool = $this->db->affected_rows();
            if(!$bool)
            {
                $msg .= '，提交审核失败';
                $st = 'fail';
            }else{
                $msg .= '，提交审核成功';
                $st = 'success';
            }
        }

        return array('status' => $st,'msg'=>$msg);
    }

    //获取业绩指标
    function get_norm($id = 0,$month=0){
        if($id >0){
            $month = $month?$month:date("Y-m");
            $sql = "select * from norm WHERE manager_id=$id and date='".$month."'";
            $rs = $this->db->query($sql)->result_array();
            if ($rs){
                foreach ($rs as &$v){
                    $v['date'] = date("Y-m",strtotime($v['date']));
                }
                return $rs[0];
            }else{
                return false;
            }

        }else{
            return false;
        }
    }
    //添加已完成指标
    public function add_finish_norm($manager_id,$date,$finish_performance=0,$finish_recruit=0,$my_recruit=0,$under_recruit=0,$my_performance=0,$under_performance=0){

       if($date && $manager_id && ($finish_performance || $finish_recruit || $my_recruit || $under_recruit || $my_performance || $under_performance)){
           //查询大客户经理本月业绩数据
           $rs = $this->db->where(array('manager_id'=>$manager_id,'date'=>$date))->get("norm")->result_array();
           if($rs){
               $completed_recruit = $rs[0]['completed_recruit']+$finish_recruit;
               $completed_performance = $rs[0]['completed_performance']+$finish_performance;
               $my_recruit = $rs[0]['my_recruit']+$my_recruit;
               $under_recruit = $rs[0]['under_recruit']+$under_recruit;
               $my_performance = $rs[0]['my_performance']+$my_performance;
               $under_performance = $rs[0]['under_performance']+$under_performance;
               $upd = array(
                   "completed_recruit"=>$completed_recruit,
                   "completed_performance"=>$completed_performance,
                   "my_recruit"=>$my_recruit,
                   "under_recruit"=>$under_recruit,
                   "my_performance"=>$my_performance,
                   "under_performance"=>$under_performance,
               );
               $this->db->update("norm",$upd,'id='.$rs[0]['id']);
           }else{
               $performace = month_norm($this->db,$manager_id);
               $insertdata = array(
                   'manager_id' => $manager_id,
                   'performance' => $performace,
                   'recruit' => 0,
                   "completed_recruit"=>$finish_recruit,
                   "completed_performance"=>$finish_performance,
                   "my_recruit"=>$my_recruit,
                   "under_recruit"=>$under_recruit,
                   "my_performance"=>$my_performance,
                   "under_performance"=>$under_performance,
                   'date' => $date
               );
               $this->db->insert("norm",$insertdata);
           }
           return $this->db->affected_rows();
       }

    }
    // 累计个人完成业绩
    function get_norm_sum($id = 0){
        if($id >0){
            $sql = "select ifnull(sum(completed_performance),0) completed_performance_sum, ifnull(sum(my_performance),0) my_performance_sum,ifnull(sum(orders),0) orders_sum from norm WHERE manager_id=".$_GET['id'] ;

            $rs = $this->db->query($sql)->result_array();
            if ($rs){
                $sql = "SELECT ifnull(count(manager_id), 0) as ssum
    FROM manager_bind AS mb
    INNER JOIN manager AS m ON m.id = mb.manager_id
    WHERE mb.manager_id <> $id AND FIND_IN_SET(mb.manager_id, queryChildrenId($id)) AND m.identity <> '试合作销售经理';";
                $re = $this->db->query($sql)->row_array();
                $rs[0]['my_recruit_sum'] = $re['ssum'];
                return $rs[0];
            }else{
                return false;
            }

        }else{
            return false;
        }
    }

    // 按月团队完成业绩
    function get_team_month_sum($pid,$month=0)
    {
        // 取得所有小弟的ID
        if($month == 0)
        {
            $month = date("Y-m");
        }
        $sun_id = find_all_sun($this->db,$pid);
        if($sun_id){
            $sun_id = implode(",",$sun_id);
            $sql = "select ifnull(sum(completed_performance),0) completed_performance_sum,ifnull(sum(completed_recruit),0) completed_recruit_sum,ifnull(sum(orders),0) orders_sum from norm WHERE manager_id in ($sun_id) AND date = '$month'" ;
            $rs = $this->db->query($sql)->result_array();
            if ($rs){
                return $rs[0];
            }else{
                return array("completed_performance_sum"=>0.00,"completed_recruit_sum"=>0,"orders_sum"=>0,"date"=>$month);
            }

        }else{
            return array("completed_performance_sum"=>0.00,"completed_recruit_sum"=>0,"orders_sum"=>0,"date"=>$month);
        }

    }

    // 上个月总业绩
    function get_last_month_sum($pid,$month=0)
    {
        if($month == 0)
        {
            $month = date("Y-m",strtotime("- 1 month"));
        }else{
            $month = date("Y-m",(strtotime("$month -1 month")));
        }
        if($pid){
            $sql = "select ifnull(completed_performance,0) completed_performance,ifnull(orders,0) orders, date from norm WHERE manager_id = $pid AND date = '$month'" ;
            $rs = $this->db->query($sql)->result_array();
            if ($rs){
                return $rs[0];
            }else{
                return array("completed_performance"=>0.00,"orders"=>0,"date"=>$month);
            }

        }else{
            return array("completed_performance"=>0.00,"orders"=>0,"date"=>$month);
        }
    }

    //平均分配指标
    function equal_distribute($id =0){
        if($id ){
            $usr = $this->users_model->get_one($_GET['id']);
            if ($usr['identity']=="大区经理"){
                //查找所有下级
                $sql = "SELECT group_concat(manager_id) mg_ids,count(manager_id) mg_count from manager_bind mb  where mb.parent_id=$id ";
                $mg = $this->db->query($sql)->row_array();
                if(!$mg['mg_count']){
                    $data['msg'] = "没有绑定大客户经理!";
                    $data['status'] = "fail";
                    $data['data'] = "";
                    return $data;
                }
                $mg_ids_arr = explode(",",$mg['mg_ids']);
                //查询指标
                $month = date("Y-m",strtotime("+ 1 month"));
                //查询已经有的指标
                $sql = "SELECT ifnull(sum(performance),0) exit_per,ifnull(sum(recruit),0) exit_rec from manager_bind mb INNER  JOIN norm ON norm.manager_id=mb.manager_id where mb.parent_id=$id  and date='".$month."'";
                $row_arr = $this->db->query($sql)->row_array();

                $norm = $this->users_model->get_norm($id,$month);
                if(!$norm){
                    $data['msg'] = "没有可以分配的指标!";
                    $data['status'] = "fail";
                    $data['data'] = "";
                    return $data;
                }

                //招募人数指标
                $recruit = $norm['recruit']-$row_arr['exit_rec'];
                //业绩指标
                $performance = $norm['performance']- $row_arr['exit_per'];
                if ($recruit<=0 && $performance<=0){
                    $data['msg'] = "指标已经分配完!";
                    $data['status'] = "fail";
                    $data['data'] = "";
                    return $data;
                    exit;
                }
                //需要设置招募指标的人数
                $recruit_count = 0;
                $performance_count = 0; //需要设置业绩指标的人数
                /******* 招募指标  *************/
                //查询所有有指标且指标大于0
                $sql = "SELECT group_concat(manager_id) mg_ids,count(manager_id) mg_count from  norm where norm.manager_id in (".$mg['mg_ids'].")  and norm.date='".$month."' and norm.recruit>0";
                $has_row_arr = $this->db->query($sql)->row_array();
                $has_count = $has_row_arr['mg_count']?$has_row_arr['mg_count']:0;
                $has_mg_ids = $has_row_arr['mg_ids'];
                $has_ids_arr = explode(",",$has_mg_ids);
                $recruit_need_ids = array_diff($mg_ids_arr,$has_ids_arr);
                //计算平均数
                $recruit_count = $mg['mg_count']-$has_count;
                $equal_recruit = ceil($recruit/$recruit_count);

                /******* 招募指标  *************/
                //查询所有有指标且指标大于0
                $sql = "SELECT group_concat(manager_id) mg_ids,count(manager_id) mg_count from  norm where norm.manager_id in (".$mg['mg_ids'].")  and norm.date='".$month."' and norm.performance>0";
                $has_row_arr = $this->db->query($sql)->row_array();
                $has_count = $has_row_arr['mg_count']?$has_row_arr['mg_count']:0;
                $has_mg_ids = $has_row_arr['mg_ids'];
                $has_ids_arr = explode(",",$has_mg_ids);
                $performance_need_ids = array_diff($mg_ids_arr,$has_ids_arr);
                //计算平均数
                $performance_count = $mg['mg_count']-$has_count;
                $equal_performance = ceil($performance/$performance_count);

                if($recruit_count==0 && $performance_count==0){
                    $data['msg'] = "所有大客户经理均已分配!";
                    $data['status'] = "fail";
                    $data['data'] = "";
                    return $data;
                }
                //合并所有需要指标或者业绩的
                $all_needs_ids = array_unique(array_merge($recruit_need_ids,$performance_need_ids));
                foreach ($all_needs_ids as $v){
                    $norm = $this->users_model->get_norm($v,$month);
                    if(!$norm){
                        $ins_arr = array(
                            "manager_id"=>$v,
                            "recruit"=>0,
                            "performance"=>0,
                            "date"=>date("Y-m",strtotime("+ 1 month"))
                        );
                        $this->db->insert("norm",$ins_arr);
                    }
                    //更新需要招募指标的
                    $upd_info = array(
                        "recruit"=>$equal_recruit
                    );
                    $map['recruit']=0;
                    $map['manager_id']=$v;
                    $this->db->where($map)->update("norm",$upd_info);
                    //更新需要业绩指标的
                    $upd_info = array(
                        "performance"=>$equal_performance
                    );
                    $map1['performance']=0;
                    $map1['manager_id']=$v;
                    $this->db->where($map1)->update("norm",$upd_info);
                }
                $data['msg']="分配成功";
                $data['status'] ="success";
                $data['data'] = "";

            }else{
                $data['msg'] = "不是大区经理";
                $data['status'] = "fail";
                $data['data'] = "";
            }
        }else{
            $data['msg'] = "参数错误";
            $data['status'] = "fail";
            $data['data'] = "";
        }
        return $data;
    }
    //业绩指标历史
    function norm_list($id=0,$start_month=0,$end_month=0,$page=1,$limit = 3){
        if($id >0){

            $sql = "select performance,completed_performance,my_performance,under_performance,recruit,completed_recruit,my_recruit,under_recruit,orders,IFNULL(completed_performance,0) - IFNULL(my_performance,0) as team_performance,date from norm WHERE manager_id=".$_GET['id'];
            if($start_month && $end_month){
                $start_arr = getthemonth($start_month);
                $end_arr = getthemonth($end_month);
                $sql.=" and date>='".$start_arr[0]."' and date<='".$end_arr[1]."'";
            }
            $limit_from = ($page-1)*$limit;
            $sql.=" limit ".$limit_from.",".$limit;
            $rs = $this->db->query($sql)->result_array();

            foreach ($rs as $k => &$v)
            {
                $date = $v['date'];
//                // 查询辖下团队业绩情况
//                $team_info = $this->users_model->get_team_month_sum($_GET['id'],$date);
//                $rs[$k]['team_info'] = $team_info;
                // 查询上月总业绩
                $last_month = $this->get_last_month_sum($id,$date);
                $rs[$k]['last_month'] = $last_month;
            }
            if ($rs){
                return $rs;
            }else{
                return false;
            }

        }else{
            return false;
        }
    }



    function add_manager_record($id=0 ,$p_id = 0,$content=""){
        if ($id && $p_id && $content){
            $data = array(
                "manager_id"=>$id,
                "parent_id"=>$p_id,
                "content"=>$content,
                "created_at"=>date("Y-m-d H:i:s")
            );
             $this->db->insert("manager_record",$data);
             return $this->db->affected_rows();
        }else{
            return false;
        }
    }

    //获取销售经理列表
    function manager_list($id = 0,$status=0,$sort_type=0,$sort_desc=0,$page=1,$limit=4,$search=0){
        $sql = "SELECT info.*,ifnull(ct.ct_count,0) ct_count from (SELECT
	manager.id,manager.name,manager.tel,manager.manager_status,manager.phone,pic_path,count(customer_order.id) order_count,
	sum(customer_order.order_money) money_sum,bind.bind_time,his.start_time,his.end_time
FROM
	manager LEFT JOIN manager_bind bind ON bind.manager_id=manager.id 
	LEFT JOIN manager_bind_history his ON his.manager_id = manager.id and his.parent_id = $id 
    LEFT join  customer_order ON manager.id = customer_order.manager_id
AND customer_order.payment_date is not null
AND customer_order.manager_id IN (
	SELECT
		manager_id
	FROM
		manager_bind
	WHERE
		parent_id = $id
 AND start_time <= customer_order.order_date 
 AND end_time > customer_order.order_date
) 
WHERE manager.is_deleted=0
	 ";
        //如果不是全部状态
        switch ($status)
        {
            case 1:
               $sql.=" and manager.id IN (
                        SELECT
                            manager_id
                        FROM
                            manager_bind
                        WHERE
                            parent_id = $id
                    ) and manager_status='正常状态'";
            break;
            case 2:
                $sql.=" and manager.id IN (
                        SELECT
                            manager_id
                        FROM
                            manager_bind
                        WHERE
                            parent_id = $id
                    ) and manager.manager_status='已离职'";
                break;
            case 3:
                $sql.=" and manager.id IN (
                        SELECT
                            manager_id
                        FROM
                            manager_bind
                        WHERE
                            parent_id = $id
                    ) and manager.manager_status='培训中'";
                break;
            case 4:   //已解绑
                // 查询
                $sql2 = "SELECT
                            manager_id
                        FROM
                            manager_bind_history
                        WHERE
                            manager_id not in (select manager_id from manager_bind where parent_id = $id)
                            and parent_id = $id
                        GROUP BY manager_id
                        ORDER BY ID DESC";
                $manager_id_jb_arr = $this->db->query($sql2)->result_array();
                $req = '';
                if($manager_id_jb_arr && is_array($manager_id_jb_arr))
                {
                    foreach ($manager_id_jb_arr as $v)
                    {
                        $req[] = $v['manager_id'];
                    }
                }
                if(!empty($req))
                {
                    $bstr = implode(',',$req);
                    $sql.=" and manager.id IN ($bstr)";
                }
                break;
            case 5:   //申请绑定中
                $sql.=" and manager.id IN (
                        SELECT manager_id from manager_bind_status where status = '申请绑定中' and parent_id = $id
                    ) ";
                break;
            default:
                $sql.=" and manager.id IN (
                        SELECT
                            manager_id
                        FROM
                            manager_bind_history
                        WHERE
                            parent_id = $id
                        UNION SELECT manager_id from manager_bind_status where status = '申请绑定中' and parent_id = $id
                    ) ";
                break;

        }
        if($search){
            $sql.=" and (manager.name like '%".$search."%' or manager.tel like '%".$search."%' or manager.phone like '%".$search."%')";
        }
        $sql.=" GROUP BY manager.id) info";
        // 销售经理的绑定时间早于客户的绑定时间
        $sql.=" LEFT JOIN (
                SELECT
				count(customer.id) ct_count,
				manager_id
			FROM customer 
			WHERE
				customer.manager_id IN (
                    SELECT
						manager_id
					FROM
						manager_bind_history
					WHERE
						parent_id = $id
                        AND start_time <= customer.created_at
                        AND end_time > customer.created_at
				)
			GROUP BY
				manager_id
		) ct ON info.id = ct.manager_id";
        switch ($sort_type)
        {
            case 1:
                $sql.=" order by info.id";
                break;
            case 2:
                $sql.=" order by ct_count";
                break;
            case 3:
                $sql.=" order by money_sum";
                break;
            case 4:
                $sql.=" order by start_time";
                break;

        }
        if ($sort_desc==1){
            $sql.=" desc";
        }else{
            $sql.=" asc";
        }
        $limit_from = ($page-1)*$limit;
        $sql.=" limit ".$limit_from.",".$limit;
        $rs = $this->db->query($sql)->result_array();
        if ($rs){
            foreach ($rs as $key=>$v){
                //查询最后一次申请状态
                $manager_id = $v['id'];
                $sql = "select * from manager_bind_status where parent_id=$id and manager_id=$manager_id order by created_at desc limit 1";
                $status = $this->db->query($sql)->row_array();
                //如果有绑定，等于是否正常状态
                if($status)
                {
                    // 绑定状态('申请绑定中','申请解绑中','绑定失败','绑定成功','解绑成功','解绑失败')
                    $rs[$key]['bind_status'] = $status['status'];
                }

                if ($v['bind_time']){
                    // 已经绑定的显示manager_status
                    $rs[$key]['show_status'] = $v['manager_status'];
                }else{
                    //否则等于申请状态
                    $rs[$key]['show_status'] = $rs[$key]['bind_status'] ? $rs[$key]['bind_status'] : '';
                }
                $rs[$key]['pic_path']= $v['pic_path'] ? $this->config->item("upload_url").$v['pic_path'] : "";
            }
            return $rs;
        }else{
            return array();
        }
    }

     //获取销售经理的订单数 ，客户数，成交额
    function get_manager_money($id = 0,$pid = 0,$month=0)
    {
        if ($id && $pid) {
            if ($month){
                $firstday = date('Y-m-01 00:00:00', strtotime($month));
                $lastday = date('Y-m-d 23:59:59', strtotime("$firstday +1 month -1 day"));
            }
            $sql = "SELECT info.*,ifnull(ct.ct_count,0) ct_count from (SELECT  manager.id,ifnull(count(customer_order.id),0) order_count, ifnull(sum(customer_order.order_money),0) money_sum FROM manager LEFT JOIN customer_order ON manager.id = customer_order.manager_id AND customer_order.payment_date is not null";
            if ($firstday && $lastday){
                $sql.=" and customer_order.payment_date BETWEEN '".$firstday."' and '".$lastday."'";
            }
            $sql.=" AND customer_order.manager_id IN ( SELECT manager_id FROM manager_bind_history WHERE manager_id = $id and  parent_id = $pid AND start_time <= customer_order.order_date AND end_time > customer_order.order_date ) WHERE manager.id = $id  and manager_status='正常状态' GROUP BY manager.id) info LEFT JOIN ( SELECT ifnull(count(customer.id),0) ct_count, manager_id FROM customer WHERE customer.manager_id IN ( SELECT manager_id FROM manager_bind_history WHERE parent_id = $pid  AND  manager_id = $id  and start_time <= customer.created_at AND end_time >= customer.created_at ) ";
            if ($firstday && $lastday){
                $sql.=" and customer.created_at BETWEEN '".$firstday."' and '".$lastday."'";
            }
            $sql.=" GROUP BY manager_id ) ct ON info.id = ct.manager_id";
            $rs = $this->db->query($sql)->result_array();
            if ($rs) {
                return $rs[0];
            } else {
                return false;
            }
        } else {
            return false;

        }
    }
//
    function get_manager_base($id = 0,$pid = 0){
        if($id && $pid){
           $sql ="  SELECT manager.id,manager.name,manager.gender,manager.email,manager.tel,manager.manager_status,manager.phone,manager.pic_path,his.start_time,his.end_time FROM manager  LEFT JOIN (SELECT MAX(start_time) start_time,max(end_time) end_time,manager_id FROM manager_bind_history GROUP BY manager_id
) his ON his.manager_id=manager.id WHERE manager.is_deleted=0 and manager_id=$id";
           $info = $this->db->query($sql)->row_array();
           if ($info){
               $info['pic_path']=$info['pic_path']?$this->config->item("upload_url").$info['pic_path']:"";
               return $info;
           }else{
               return false;
           }
        }else{
            return false;
        }
    }
    //获取维护记录
    function get_manager_record($id =0, $pid =0,$page=1,$limit=3){

       $sql= "select * from manager_record where manager_id=$id and parent_id=$pid ORDER by id desc ";
        $limit_from = ($page-1)*$limit;
        $sql.=" limit ".$limit_from.",".$limit;

        return $this->db->query($sql)->result_array();
    }

    //总榜
    function manager_sort_list($id = 0,$type=0,$limit = 0){
        if ($type == "month"){
            $start =  date("Y-m-d H:i:s",mktime(0, 0 , 0,date("m"),1,date("Y")));
            $end =  date("Y-m-d H:i:s",mktime(23,59,59,date("m"),date("t"),date("Y")));

        }elseif($type=="season"){
            $season = ceil((date('n'))/3);//当月是第几季度
            $start =  date('Y-m-d H:i:s', mktime(0, 0, 0,$season*3-3+1,1,date('Y')));
            $end =  date('Y-m-d H:i:s', mktime(23,59,59,$season*3,date('t',mktime(0, 0 , 0,$season*3,1,date("Y"))),date('Y')));

        }
        $sql = "  select manager.name,ifnull(count(customer_order.id),0) order_count,ifnull(sum(customer_order.order_money),0) order_money from manager left join customer_order on manager.id = customer_order.manager_id ";
        $sql .=" and customer_order.manager_id in (select manager_id from manager_bind_history where parent_id = $id and start_time <= customer_order.order_date and end_time >= customer_order.order_date) 
	and customer_order.payment_date is not null";
	 if ($start && $end){
            $sql.=" and customer_order.payment_date BETWEEN '".$start."' and '".$end."'";
        }
$sql.=" where manager.is_deleted=0 and manager.id in (select manager_id from manager_bind_history where parent_id = $id and end_time >= '2017-7-1')  
GROUP BY manager.id 
order by sum(customer_order.order_money) desc , manager.name desc";
        $sql.=" limit ".$limit;
        $rs = $this->db->query($sql)->result_array();
        if ($rs){
            return $rs;
        }else{
            return false;
        }

    }

    function get_message_count($id=0){
        $sql = "select count(*) count from message where have_read=0 and manager_id=$id";
        $row = $this->db->query($sql)->row_array();
        return $row['count'];
    }

    //数据统计(销售经理)
    function data_count($id,$year,$month){
        if ($id && $year && $month) {
            $date_str = $year."-".$month;
            //创建的客户数
            $sql = "select count(id) c_count from customer where date_format(created_at,'%Y-%m')='".$date_str."' and manager_id=".$id;

            //当月订单总数
            $sql1 = "select count(id) d_count from customer_order where date_format(order_date,'%Y-%m')='".$date_str."' and manager_id=".$id;

            //成交金额
            $sql2 = "select my_performance from norm where date = '".$date_str."' and manager_id=".$id;

            // 新增招募销售人数统计
            $sql3 = "select count(1) AS my_recruit from manager_bind where date_format(bind_time,'%Y-%m')='".$date_str."' and parent_id=".$id;
//            $sql3 = "select my_recruit from norm where date = '".$date_str."' and manager_id=".$id;

            $rs = $this->db->query($sql)->result_array();


            $rs1 = $this->db->query($sql1)->result_array();

            $rs2 =  $this->db->query($sql2)->result_array();

            $rs3 = $this->db->query($sql3)->row_array();

            $data1['customr_count'] = $rs[0]['c_count'];
            $data1['order_count'] = $rs1[0]['d_count'];
            $data1['my_performance'] = $rs2[0]['my_performance']?$rs2[0]['my_performance']:0;
            $data1['my_recruit'] = $rs3['my_recruit']?$rs3['my_recruit']:0;

            $data['msg'] = "获取成功";
            $data['data'] = $data1;
            $data['status'] = "success";
        }else{
            $data['msg'] = "参数错误";
            $data['status'] = "fail";
            $data['data'] = "";
        }
        return $data;

    }

     //数据统计2大客户经理
    function data_count_ciy($id,$year,$month){
        if ($id && $year && $month) {
            $date_str = $year."-".$month;
            //创建的客户数
            $sql = "select count(id) c_count from customer where date_format(created_at,'%Y-%m')='".$date_str."' and manager_id=".$id;

            //当月订单总数
            $sql1 = "select count(id) d_count from customer_order where date_format(order_date,'%Y-%m')='".$date_str."' and manager_id=".$id;

            //成交金额
            $sql2 = "select my_performance from norm where date = '".$date_str."' and manager_id=".$id;

            // 新增招募销售人数统计
//            $sql3 = "select count(1) as my_recruit from manager_bind where date_format(bind_time,'%Y-%m')='".$date_str."' and parent_id=".$id;
            $sql3 = "select my_recruit from norm where date = '".$date_str."' and manager_id=".$id;

            $rs = $this->db->query($sql)->result_array();


            $rs1 = $this->db->query($sql1)->result_array();

            $rs2 =  $this->db->query($sql2)->result_array();

            $rs3 = $this->db->query($sql3)->row_array();

            $data1['customr_count'] = $rs[0]['c_count'];
            $data1['order_count'] = $rs1[0]['d_count'];
            $data1['my_performance'] = $rs2[0]['my_performance']?$rs2[0]['my_performance']:0;
            $data1['my_recruit'] = $rs3['my_recruit']?$rs3['my_recruit']:0;

            $data['msg'] = "获取成功";
            $data['data'] = $data1;
            $data['status'] = "success";
        }else{
            $data['msg'] = "参数错误";
            $data['status'] = "fail";
            $data['data'] = "";
        }
        return $data;
    }
     //数据统计，大区经理
     function data_count_district($id,$year,$month){
         if ($id && $year && $month) {
             $date_str = $year."-".$month;
             //创建的客户数
             $sql = "select count(id) c_count from customer where date_format(created_at,'%Y-%m')='".$date_str."' and manager_id=".$id;

             //当月订单总数
             $sql1 = "select count(id) d_count from customer_order where date_format(order_date,'%Y-%m')='".$date_str."' and manager_id=".$id;

             //成交金额
             $sql2 = "select my_performance from norm where date = '".$date_str."' and manager_id=".$id;

             // 新增招募销售人数统计
//            $sql3 = "select count(1) AS mg_count from manager_bind where date_format(bind_time,'%Y-%m')='".$date_str."' and parent_id=".$id;
             $sql3 = "select my_recruit from norm where date = '".$date_str."' and manager_id=".$id;

             $rs = $this->db->query($sql)->result_array();


             $rs1 = $this->db->query($sql1)->result_array();

             $rs2 =  $this->db->query($sql2)->result_array();

             $rs3 = $this->db->query($sql3)->row_array();

             $data1['customr_count'] = $rs[0]['c_count'];
             $data1['order_count'] = $rs1[0]['d_count'];
             $data1['my_performance'] = $rs2[0]['my_performance']?$rs2[0]['my_performance']:0;
             $data1['my_recruit'] = $rs3['my_recruit']?$rs3['my_recruit']:0;

             $data['msg'] = "获取成功";
             $data['data'] = $data1;
             $data['status'] = "success";
         }else{
             $data['msg'] = "参数错误";
             $data['status'] = "fail";
             $data['data'] = "";
         }
         return $data;

     }




    //获取关联时间
    function get_last_bind_history($id=0,$pid = 0){
        $sql= "SELECT * FROM manager_bind_history where manager_id=$id and parent_id=$pid ORDER BY start_time DESC ";
        return $this->db->query($sql)->result_array();
    }


    function validate($data1=null){
        $arr = $data1;
        if($arr['email']){
            if(!is_email($arr['email'])){
                $data['status'] = "fail";
                $data['msg'] = "邮箱格式错误";
                $data['data'] = "";
                return $data;
            }
        }
        if($arr['tel']){
            if(!is_mobile($arr['tel'])){
                $data['status'] = "fail";
                $data['msg'] = "手机格式错误";
                $data['data'] = "";
                return $data;
            }
        }
        if($arr['phone']){
            if(!is_mobile($arr['phone'])){
                $data['status'] = "fail";
                $data['msg'] = "手机格式错误";
                $data['data'] = "";
                return $data;
            }
        }
        if($arr['name']){
            if(!check_name($arr['name'])){
                $data['status'] = "fail";
                $data['msg'] = "姓名必须是中文和字母,不能超过10位!";
                $data['data'] = "";
                return $data;
            }
            $arr['id'] = $arr['id']? $arr['id']:0;
            $exit_usr = $this->users_model->get_other($arr['name'],$arr['id']);
            if($exit_usr){
                $data['status'] = "fail";
                $data['msg'] = "该姓名已经存在!";
                $data['data'] = "";
                return $data;
            }
        }
        $data['status'] = "success";
        return $data;
    }


    //个人资料修改
    function upd_user_info( $id=0,$arr= null){
        if($id && $arr){
            $arr['id'] = $id;
            $rs = $this->validate($arr);
            if($rs['status']=="fail"){
                return $rs;
            }
            $upd['id'] = $id;
            $upd['is_deleted'] =0;
            $bool=$this->db->where($upd)->update('manager',$arr);
            if ($bool){
                $data['status'] = "success";
                $data['msg'] = "修改成功";
                $data['data'] ='';
            }else{
                $data['status'] = "fail";
                $data['msg'] = "修改失败";
                $data['data'] ='';
            }
        }else{
            $data['status'] = "fail";
            $data['msg'] = "参数错误";
            $data['data'] = "";
        }
        return $data;
    }

    // 根据时间获取评分
    function score_month_sum($begin_time,$end_time,$mid)
    {
        $sql = "select ifnull(sum(score),0) as total FROM score WHERE manager_id = $mid AND created_at >= '$begin_time' AND created_at <= '$end_time' ";
        $rs = $this->db->query($sql)->row_array();
        if($rs)
        {
            return $rs;
        }
        return false;
    }

    // 插入晋升表
    function add_upgrade($id,$pid='',$push_money=0,$now_identity,$next_identity,$type='晋升')
    {
        $sql = "SELECT * FROM upgrade WHERE manager_id = $id AND manager_from = '$now_identity' AND manager_to = '$next_identity' AND type = '$type' limit 1";
        $re = $this->db->query($sql)->row_array();
        $message = '提交失败';
        if($re)
        {
            if($re['status'] == '审核通过')
            {
                $message = "已经审核通过，无需重复申请";
            }else{
//                $bool = $this->db->where(array('id'=>$re['id']))->update("upgrade",array('status'=>'待审核','parent_id'=>$pid,'push_money'=>$push_money));
                    if($re['status'] == '审核未通过'){
                        $data['manager_id'] = $id;
                        $data['parent_id'] = $pid;
                        $data['push_money'] = $push_money;
                        $data['manager_from'] = $now_identity;
                        $data['manager_to'] = $next_identity;
                        $data['status'] = '待审核';
                        $data['type'] = $type;
                        $data['created_at'] = date('Y-m-d H:i:s',time());
                        $bool = $this->db->insert("upgrade",$data);
                        $message = "审核未通过";
                        if($bool)
                        {
                            $message .= "，已经重新提交审核";
                        }
                    }else{
                        $message = "已经提交过申请，请耐心等待审核";
                    }
            }
        }else{
            $data['manager_id'] = $id;
            $data['parent_id'] = $pid;
            $data['push_money'] = $push_money;
            $data['manager_from'] = $now_identity;
            $data['manager_to'] = $next_identity;
            $data['status'] = '待审核';
            $data['type'] = $type;
            $data['created_at'] = date('Y-m-d H:i:s',time());
            $bool = $this->db->insert("upgrade",$data);
            if($bool)
            {
                $message = "提交申请成功";
            }
        }
        return $message;
    }

    // 评分明细列表
    function manager_score_list($id=0,$page=1,$limit = 3,$year,$month){
        if($id >0){
            $from_date = $year.'-'.$month.'-01';
            $end_date = date('Y-m-d', strtotime("$from_date +1 month -1 day"))." 23-59-59";
            $from_date .= " 00-00-00";
            $sql = "SELECT * from score WHERE manager_id = $id and created_at between '$from_date' and '$end_date' ORDER BY created_at desc";
            $limit_from = ($page-1)*$limit;
            $sql.=" limit ".$limit_from.",".$limit;
            $rs = $this->db->query($sql)->result_array();

            if ($rs){
                // 返回一个新增成员的身份
                foreach ($rs as &$r)
                {
                    $r['score'] = floatval($r['score']);
                    if($r['new_manager_id'])
                    {
                        $sql = "SELECT identity FROM manager WHERE id = ".$r['new_manager_id'];
                        $identity = $this->db->query($sql)->row_array()['identity'];
                        $r['new_manager_identity'] = $identity;
                    }
                    if(preg_match('/业绩/',$r['reason']))
                    {
                        $r['type'] = 'norm';
                    }else{
                        $r['type'] = '';
                    }
                }
                return $rs;
            }else{
                return false;
            }

        }else{
            return false;
        }
    }

    // 奖金按月统计
    function manager_push_money_sum($id,$year,$month)
    {
        $date_str = $year."-".$month;
        $sql = "SELECT sum(total_money) as total_money
FROM
  (SELECT
      pm.parent_id,
      pm.created_at,
      IFNULL(sum(pm.push_money),0) as total_money
   FROM push_money AS pm
   UNION ALL
   SELECT
      up.parent_id,
      up.created_at,
      IFNULL(sum(up.push_money),0) as total_money
   FROM upgrade AS up WHERE up.status = '审核成功') AS t
  WHERE t.parent_id = $id and date_format(t.created_at,'%Y-%m')='$date_str'
";
        $rs = $this->db->query($sql)->row_array();
        return $rs['total_money'] ? $rs['total_money'] : 0;
    }

    // 奖金明细列表
    function manager_push_money_list($id=0,$page=1,$limit = 3,$keyword){
        if($id >0){

            $sql = "SELECT *
FROM
  (SELECT
     pm.created_at,
     pm.push_money,
     pm.manager_id,
     m.name,
     m.identity,
     pm.parent_id,
     pm.id,
     '1' AS type,
     pm.order_id,
     pm.money,
     ''  AS from_identify,
     ''  as to_identify
   FROM push_money AS pm LEFT JOIN manager m ON pm.manager_id = m.id
   UNION ALL
   SELECT
     up.created_at,
     up.push_money,
     up.manager_id,
     m.name,
     m.identity,
     up.parent_id,
     up.id,
     '2' AS type,
     ''  AS order_id,
     ''  AS money,
     up.manager_from as from_identify,
     up.manager_to as to_identify
   FROM upgrade AS up LEFT JOIN manager m ON up.manager_id = m.id WHERE up.status = '审核成功') AS t
  WHERE t.parent_id = $id";
            if($keyword){
                $sql .= " and t.name like '%$keyword%' ";
            }
            $sql .= " ORDER BY t.created_at DESC";
            $limit_from = ($page-1)*$limit;
            $sql.=" limit ".$limit_from.",".$limit;
            $rs = $this->db->query($sql)->result_array();

            if ($rs){
                return $rs;
            }else{
                return false;
            }

        }else{
            return false;
        }
    }

    // 奖金详情
    function manager_push_money_detail($id, $type)
    {
        if($type == 1)
        {
            $sql = "SELECT pm.*,m.identity,m.name as name,co.order_bn,co.order_status,co.product_count,co.completed_date FROM push_money AS pm LEFT JOIN manager m ON pm.manager_id = m.id LEFT JOIN customer_order co ON co.id = pm.order_id WHERE pm.id = $id";
        }
        elseif ($type == 2)
        {
            $sql = "SELECT up.*,m.identity,m.name FROM upgrade AS up LEFT JOIN manager m ON up.manager_id = m.id WHERE up.id = $id";
        }else{
            return false;
        }

        $rs = $this->db->query($sql)->result_array();

        if ($rs){
            return $rs;
        }else{
            return false;
        }
    }

}